<?php

namespace app\admin\controller;


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use app\common\model\Competitor as CompetitorModel;

class Testexcel extends Base
{
    protected $model;

    protected function initialize()
    {
        parent::initialize();
        $this->model = CompetitorModel::getInstance();
    }


    /**
     * 导出excel表
     * $data：要导出excel表的数据，接受一个二维数组
     * $name：excel表的表名
     * $head：excel表的表头，接受一个一维数组
     * $key：$data中对应表头的键的数组，接受一个一维数组
     * 备注：此函数缺点是，表头（对应列数）不能超过26；
     *循环不够灵活，一个单元格中不方便存放两个数据库字段的值
     */
    public function outdata($name = '测试表', $data = [], $head = [], $keys = [], $option = [])
    {
        //计算表头数量
        $count = count($head);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        if(isset($option['mergeCell'])){
            //合并单元格
            $sheet->setMergeCells($option['mergeCell']);
            //设置合并后单元格内容
            $sheet->setCellValue('A1', $name);
            //设置居中
            $sheet->getStyleByColumnAndRow(1, 1)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        }

        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头：
            $sheet->setCellValue(strtoupper(chr($i)) . '2', $head[$i - 65]);
            //设置居中
            $sheet->getStyleByColumnAndRow($i - 65, 2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        }

        /*--------------开始从数据库提取信息插入Excel表中------------------*/

        //循环设置单元格：
        foreach ($data as $key => $item) {
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
            for ($i = 65; $i < $count + 65; $i++) {
                //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 3), $item[$keys[$i - 65]]);
                //设置居中
                $sheet->getStyleByColumnAndRow($i - 65, $key + 3)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                //固定列宽
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20);
            }
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }


    public function demo()
    {
        $data = $this->model
            ->field(['create_time', 'competitor_num', 'name', 'tel', 'first_image', 'remark', 'fake_ticket_num + ticket_num as sum_ticket_num', 'fake_money_num + money_num as sum_money_num'])
            ->order('sum_ticket_num desc')
            ->select();
        //排名
        foreach ($data as $k => &$value) {
            $value['order'] = $k + 1;
            $value['create_time'] = date("Y-m-d H:i:s", $value['create_time']);
        }

        $field = ['competitor_num', 'name', 'tel', 'first_image', 'sum_ticket_num', 'sum_money_num', 'create_time', 'order'];
        $title = ['编号', '姓名', '手机号码', '图片链接', '票数', '礼物', '参加时间', '排序/名次'];

        $count = count($title);
        $option['mergeCell'] = ['A1:' . strtoupper(chr(65 + $count - 1)) . '1' => 'A1:' . strtoupper(chr(65 + $count - 1)) . '1'];
        $this->outdata("测试表", $data, $title, $field, $option);
    }

}